﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data.SqlClient;
namespace syc.Produce.Report
{
    public partial class 产品产销存月报表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double 油管本期入库;
        protected double 套管本期入库;
        protected double 套管N80本期入库;

        protected double 油管本年累计入库;
        protected double 套管本年累计入库;
        protected double 套管N80本年累计入库;

        protected double 油管本期出库;
        protected double 套管本期出库;
        protected double 套管N80本期出库;

        protected double 油管本年累计出库;
        protected double 套管本年累计出库;
        protected double 套管N80本年累计出库;

        protected double 油管本期结存;
        protected double 套管本期结存;
        protected double 套管N80本期结存;       

        protected int year;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Month0.SelectedIndex = DateTime.Today.Month - 1;
                this.Year0.Text = DateTime.Today.Year.ToString();

            }
            Button_Query_Click(null, null);
        }

        protected void WriteToFile()
        {
            DateTime d;

            d = DateTime.Today;

            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Produce/模板/产品产销存月报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "产品产销存月报表";
            hssfworkbook.SummaryInformation = si;
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("产品产销存月报表");
            Button_Query_Click(null, null);
            sheet1.GetRow(1).GetCell(5).SetCellValue(DateTime.Today.Year + "年" + DateTime.Today.Month + "月");
            //sheet1.GetRow(17).GetCell(4).SetCellValue(DateTime.Today.Year+"年"+DateTime.Today.Month+"月");
            sheet1.GetRow(5).GetCell(1).SetCellValue(油管本期结存 + 油管本期出库 - 油管本期入库);
            sheet1.GetRow(5).GetCell(2).SetCellValue(油管本期入库);
            sheet1.GetRow(5).GetCell(3).SetCellValue(油管本年累计入库);
            sheet1.GetRow(5).GetCell(4).SetCellValue(油管本期出库);
            sheet1.GetRow(5).GetCell(5).SetCellValue(油管本年累计出库);
            sheet1.GetRow(5).GetCell(6).SetCellValue(油管本期出库);
            sheet1.GetRow(5).GetCell(7).SetCellValue(油管本年累计出库);
            sheet1.GetRow(5).GetCell(12).SetCellValue(油管本期结存);

            sheet1.GetRow(6).GetCell(1).SetCellValue(套管本期结存 + 套管本期出库 - 套管本期入库);
            sheet1.GetRow(6).GetCell(2).SetCellValue(套管本期入库);
            sheet1.GetRow(6).GetCell(3).SetCellValue(套管本年累计入库);
            sheet1.GetRow(6).GetCell(4).SetCellValue(套管本期出库);
            sheet1.GetRow(6).GetCell(5).SetCellValue(套管本年累计出库);
            sheet1.GetRow(6).GetCell(6).SetCellValue(套管本期出库);
            sheet1.GetRow(6).GetCell(7).SetCellValue(套管本年累计出库);
            sheet1.GetRow(6).GetCell(12).SetCellValue(套管本期结存);

            sheet1.GetRow(7).GetCell(1).SetCellValue(套管N80本期结存 + 套管N80本期出库 - 套管N80本期入库);
            sheet1.GetRow(7).GetCell(2).SetCellValue(套管N80本期入库);
            sheet1.GetRow(7).GetCell(3).SetCellValue(套管N80本年累计入库);
            sheet1.GetRow(7).GetCell(4).SetCellValue(套管N80本期出库);
            sheet1.GetRow(7).GetCell(5).SetCellValue(套管N80本年累计出库);
            sheet1.GetRow(7).GetCell(6).SetCellValue(套管N80本期出库);
            sheet1.GetRow(7).GetCell(7).SetCellValue(套管N80本年累计出库);
            sheet1.GetRow(7).GetCell(12).SetCellValue(套管N80本期结存);

            
          

            //sheet1.GetRow(25).GetCell(8).SetCellValue("报出日期："+DateTime.Today.Year+"-"+DateTime.Today.Month+"-"+DateTime.Today.Day);
            //sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            year = int.Parse(this.Year0.Text);
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            try
            {
                Conn.Open();
                SqlCommand Cmd = new SqlCommand();
                Cmd.Connection = Conn;
                string sql1 = "select dbo.产品本月实际产量(*年份*,*月份*,1),dbo.产品本月实际产量(*年份*,*月份*,2),dbo.产品本月实际产量(*年份*,*月份*,6)";
                sql1 = sql1.Replace("*年份*", year.ToString());
                sql1 = sql1.Replace("*月份*", this.Month0.Text.ToString());
                Cmd.CommandText = sql1;
                SqlDataReader Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    油管本期入库 = double.Parse(Reader.GetDecimal(0).ToString());
                    套管本期入库 = double.Parse(Reader.GetDecimal(1).ToString());
                    套管N80本期入库 = double.Parse(Reader.GetDecimal(2).ToString());
                }
                Reader.Close();
                string sql2 = "select dbo.产品某月止累计(*年份*,*月份*,1),dbo.产品某月止累计(*年份*,*月份*,2),dbo.产品某月止累计(*年份*,*月份*,6)";
                sql2 = sql2.Replace("*年份*", year.ToString());
                sql2 = sql2.Replace("*月份*", this.Month0.Text.ToString());
                Cmd.CommandText = sql2;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    油管本年累计入库 = double.Parse(Reader.GetDecimal(0).ToString());
                    套管本年累计入库 = double.Parse(Reader.GetDecimal(1).ToString());
                    套管N80本年累计入库 = double.Parse(Reader.GetDecimal(2).ToString());
                }
                Reader.Close();
                string sql3 = "select dbo.[产品本期发出](*年份*,*月份*,1,0),dbo.[产品本期发出](*年份*,*月份*,2,0),dbo.[产品本期发出](*年份*,*月份*,4,0)";
                sql3 = sql3.Replace("*年份*", year.ToString());
                sql3 = sql3.Replace("*月份*", this.Month0.Text.ToString());
                Cmd.CommandText = sql3;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    油管本期出库 = Reader.GetDouble(0);
                    套管本期出库 = Reader.GetDouble(1);
                    套管N80本期出库 = Reader.GetDouble(2);
                }
                Reader.Close();
                string sql4 = "select dbo.[产品本期发出累计](*年份*,*月份*,1,0),dbo.[产品本期发出累计](*年份*,*月份*,2,0),dbo.[产品本期发出累计](*年份*,*月份*,4,0)";
                sql4 = sql4.Replace("*年份*", year.ToString());
                sql4 = sql4.Replace("*月份*", this.Month0.Text.ToString());
                Cmd.CommandText = sql4;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    油管本年累计出库 = Reader.GetDouble(0);
                    套管本年累计出库 = Reader.GetDouble(1);
                    套管N80本年累计出库 = Reader.GetDouble(2);
                }
                Reader.Close();
                string sql5 = "select dbo.[产品本期结存](*年份*,*月份*,1,0),dbo.[产品本期结存](*年份*,*月份*,2,0),dbo.[产品本期结存](*年份*,*月份*,4,0)";
                sql5 = sql5.Replace("*年份*", year.ToString());
                sql5 = sql5.Replace("*月份*", this.Month0.Text.ToString());
                Cmd.CommandText = sql5;
                Reader = Cmd.ExecuteReader();
                if (Reader.Read())
                {
                    油管本期结存 = Reader.GetDouble(0);
                    套管本期结存 = Reader.GetDouble(1);
                    套管N80本期结存 = Reader.GetDouble(2);
                }
                Reader.Close();


            }
            catch (Exception ex)
            {
                Conn.Close();
                return;
                throw ex;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        protected void Year0_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void Month0_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}
